﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Linq;
using System.Xml;
using System.Reflection;
using System.Xml.Xsl;
using System.Diagnostics;

namespace System.Data.DataSetGenerator
{
    public class SqlDataAdapterGenerator : 
        IMessageSender
    {
        public SqlDataAdapterGenerator(SqlConnection connection, IDataAdapterDescriptor descriptor)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            if (descriptor == null)
                throw new ArgumentNullException("descriptor");

            if (String.IsNullOrEmpty(descriptor.TableName))
                throw new ApplicationException("TableName is null");

            this.Connection = connection;
            this.Descriptor = descriptor;
            
        }

        private SqlConnection Connection { get; set; }

        private IDataAdapterDescriptor Descriptor { get; set; }

        private static SqlCommandBuilder CommandBuilder = new SqlCommandBuilder();

        public void WriteCode(Stream stream)
        {
            StreamWriter writer = new StreamWriter(stream);
            WriteCode(writer);
            writer.Flush();
        }

        public void WriteCode(TextWriter stream)
        {
            SendMessage(String.Format("Building DataAdapter for {0}...", Descriptor.TableName), TraceLevel.Info);

            XmlDocument xmlDocument = new XmlDocument();
            XmlElement dataAdapterElement = xmlDocument.CreateElement("DataAdapter");
            dataAdapterElement.SetAttribute("TableName", Descriptor.TableName);

            xmlDocument.AppendChild(dataAdapterElement);

            var tableName = Descriptor.TableName;

            DataTable dataTable = Util.GetDataTableWithSchema(Connection, tableName);

            ColumnFilter columnFilter = new ColumnFilter();
            columnFilter.IgnoreComputedColumns = Descriptor.IgnoreComputedColumnsForInsertAndUpdate;

            SqlDataAdapter adapter = Util.CreateDataAdapter
                (Connection, tableName, Descriptor.ConflictOption, columnFilter);
                            
            XmlElement selectCommandElement = xmlDocument.CreateElement("SelectCommand");
            XmlElement insertCommandElement = xmlDocument.CreateElement("InsertCommand");
            XmlElement updateCommandElement = xmlDocument.CreateElement("UpdateCommand");
            XmlElement deleteCommandElement = xmlDocument.CreateElement("DeleteCommand");

            dataAdapterElement.AppendChild(selectCommandElement);
            dataAdapterElement.AppendChild(insertCommandElement);
            dataAdapterElement.AppendChild(updateCommandElement);
            dataAdapterElement.AppendChild(deleteCommandElement);

            FillCommand(selectCommandElement, adapter.SelectCommand);
            FillCommand(insertCommandElement, adapter.InsertCommand);
            insertCommandElement.SetAttribute("UpdatedRowSource", Descriptor.UpdatedRowSource.ToString());
            FillCommand(updateCommandElement, adapter.UpdateCommand);
            updateCommandElement.SetAttribute("UpdatedRowSource", Descriptor.UpdatedRowSource.ToString());
            FillCommand(deleteCommandElement, adapter.DeleteCommand);
 
            // Append Select Clause to the insert and update commands

            if (Descriptor.UpdatedRowSource == UpdateRowSource.FirstReturnedRecord ||
                Descriptor.UpdatedRowSource == UpdateRowSource.Both)
            {
                var columns = dataTable.Columns.OfType<DataColumn>();
                var allColumnNames = columns.Select(column => CommandBuilder.QuoteIdentifier(column.ColumnName)).ToArray();
                var joinedColumnNames = String.Join(", ", allColumnNames);
                String selectScopeClause = String.Format("select " + joinedColumnNames + " from {0} ", CommandBuilder.QuoteIdentifier(dataTable.TableName));

                // Tune up InsertCommand
                var identityColumn = columns.FirstOrDefault(column => column.AutoIncrement == true);
                if (identityColumn != null)
                {
                    var selectScopeForInsertClause = 
                        selectScopeClause + " where " + CommandBuilder.QuoteIdentifier(identityColumn.ColumnName) + " = SCOPE_IDENTITY()";
                    insertCommandElement.SelectSingleNode("CommandText").InnerText += "; " + selectScopeForInsertClause;
                }
                else if (dataTable.PrimaryKey != null)
                {
                    var pkColumnNames = dataTable.PrimaryKey.Select(column => CommandBuilder.QuoteIdentifier(column.ColumnName) + " = @" + column.ColumnName).ToArray();
                    var selectScopeForInsertClause =
                        selectScopeClause + " where " + String.Join(" AND ", pkColumnNames);
                    insertCommandElement.SelectSingleNode("CommandText").InnerText += "; " + selectScopeForInsertClause;
                }

                // Tune up UpdateCommand
                if (dataTable.PrimaryKey != null && dataTable.PrimaryKey.Length != 0)
                {
                    var pkColumnNames = dataTable.PrimaryKey.Select(c => CommandBuilder.QuoteIdentifier(c.ColumnName) + " = @Original_" + c.ColumnName).ToArray();
                    var selectScopeForUpdateClause = 
                        selectScopeClause + " where " + String.Join(" AND ", pkColumnNames);
                    updateCommandElement.SelectSingleNode("CommandText").InnerText += "; " + selectScopeForUpdateClause;
                }
            }

            Stream xslStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("System.Data.DataSetGenerator.SqlDataAdapterGenerator.xslt");
            xslStream.Position = 0;
            XmlDocument xsltDocument = new XmlDocument();
            xsltDocument.Load(xslStream);
            xslStream.Close();

            XslCompiledTransform xslt = new XslCompiledTransform();
            XsltSettings settings = new XsltSettings(true, true);

            xslt.Load(xsltDocument, settings, null);
            
            XsltArgumentList xslArgs = new XsltArgumentList();
            xslArgs.AddParam("Namespace", "", Descriptor.DataAdaptersNamespace);

            if (!String.IsNullOrEmpty(Descriptor.BaseClass))
                xslArgs.AddParam("BaseClass", "", Descriptor.BaseClass);

            xslt.Transform(xmlDocument, xslArgs, stream);
            stream.Flush();
        }

        private void FillCommand(XmlElement commandElement, SqlCommand command)
        {
            // Select command
            var commandTextElement = commandElement.OwnerDocument.CreateElement("CommandText");
            commandTextElement.InnerText = command.CommandText;
            commandElement.AppendChild(commandTextElement);

            var parametersElement = commandElement.OwnerDocument.CreateElement("Parameters");
            commandElement.AppendChild(parametersElement);

            foreach (SqlParameter parameter in command.Parameters)
            {
                var parameterElement = commandElement.OwnerDocument.CreateElement("Parameter");
                parameterElement.SetAttribute("ParameterName", "", parameter.ParameterName);
                parameterElement.SetAttribute("DbType", "", parameter.DbType.ToString());
                parameterElement.SetAttribute("Direction", "", parameter.Direction.ToString());
                parameterElement.SetAttribute("IsNullable", "", parameter.IsNullable.ToString());
                parameterElement.SetAttribute("SqlDbType", "", parameter.SqlDbType.ToString());
                parameterElement.SetAttribute("SourceColumn", "", parameter.SourceColumn);
                parameterElement.SetAttribute("SourceVersion", "", parameter.SourceVersion.ToString());
                parameterElement.SetAttribute("SourceColumnNullMapping", "", parameter.SourceColumnNullMapping.ToString());
                parametersElement.AppendChild(parameterElement);
            }
        }

        protected void SendMessage(String message, TraceLevel level)
        {
            if (Message != null)
            {
                MessageEventArgs e = new MessageEventArgs(message, level);
                Message(this, e);
            }
        }

        public event MessageEventHandler Message;
    }
}
